/* Copyright (c) JForum Team
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms,
 * with or without modification, are permitted provided
 * that the following conditions are met:
 * 
 * 1) Redistributions of source code must retain the above
 * copyright notice, this list of conditions and the
 * following  disclaimer.
 * 2)  Redistributions in binary form must reproduce the
 * above copyright notice, this list of conditions and
 * the following disclaimer in the documentation and/or
 * other materials provided with the distribution.
 * 3) Neither the name of "Rafael Steil" nor
 * the names of its contributors may be used to endorse
 * or promote products derived from this software without
 * specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT
 * HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
 * BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
 * THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE
 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA,
 * OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
 * IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
 * ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
 * ADVISED OF THE POSSIBILITY OF SUCH DAMAGE
 *
 * The JForum Project
 * http://www.jforum.net
 */
package net.jforum.dao.generic;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.jforum.JForumExecutionContext;
import net.jforum.dao.DataAccessDriver;
import net.jforum.dao.SummaryDAO;
import net.jforum.entities.Post;
import net.jforum.entities.Topic;
import net.jforum.entities.User;
import net.jforum.exceptions.DatabaseException;
import net.jforum.util.DbUtils;
import net.jforum.util.preferences.ConfigKeys;
import net.jforum.util.preferences.SystemGlobals;

/**
 * @author Franklin Samir (franklin (at) portaljava [dot] com)
 * @version $Id: GenericSummaryDAO.java,v 1.11 2006/08/23 02:13:41 rafaelsteil Exp $
 */
public class GenericSummaryDAO extends AutoKeys implements SummaryDAO
{
	/**
	 * @see net.jforum.dao.SummaryDAO#selectById(Date, Date)
	 */
	public List selectLastPosts(Date firstDate, Date lastDate)
	{
		String query = SystemGlobals.getSql("SummaryDAO.selectPosts");
		PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(query);
			p.setTimestamp(1, new Timestamp(firstDate.getTime()));
			p.setTimestamp(2, new Timestamp(lastDate.getTime()));

			List posts = new ArrayList();
			rs = p.executeQuery();

			while (rs.next()) {
				posts.add(this.fillPost(rs));
			}

			return posts;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
	}

	private Post fillPost(ResultSet rs) throws SQLException
	{
		Post post = new Post();

		post.setId(rs.getInt("post_id"));
		post.setTopicId(rs.getInt("topic_id"));
		post.setForumId(rs.getInt("forum_id"));
		post.setUserId(rs.getInt("user_id"));
		Timestamp postTime = rs.getTimestamp("post_time");
		post.setTime(postTime);
		post.setSubject(rs.getString("post_subject"));
		post.setText(rs.getString("post_text"));
		post.setPostUsername(rs.getString("username"));

		SimpleDateFormat df = new SimpleDateFormat(SystemGlobals.getValue(ConfigKeys.DATE_TIME_FORMAT));
		post.setFormatedTime(df.format(postTime));

		post.setKarma(DataAccessDriver.getInstance().newKarmaDAO().getPostKarma(post.getId()));

		return post;
	}

	public List listRecipients()
	{
		String query = SystemGlobals.getSql("SummaryDAO.selectAllRecipients");
		PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(query);

			List recipients = new ArrayList();
			rs = p.executeQuery();

			String mail = null;
			while (rs.next()) {
				mail = rs.getString("user_email");
				if (mail != null && !mail.trim().equals("")) {
					recipients.add(mail);
				}
			}

			return recipients;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
	}

	@Override
	/*
	 * get the count of the new  topics today
	 */
	public int countTopicsByForumByTimeLimit(Date startDate ,Date endDate,int forumId) {
		String query = SystemGlobals.getSql("SummaryDAO.countTopicsByForumByTimeLimit");
		PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(query);
			p.setInt(1, forumId);
 			p.setTimestamp(2, new Timestamp(startDate.getTime()));	
 			p.setTimestamp(3, new Timestamp(endDate.getTime()));	
			rs = p.executeQuery();
            int count=0;
			if(rs.next())
				count=rs.getInt(1);
			return count;
			
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
	 
	}

	@Override
	public Map countTopicWithForumIdsByTimeLimit(Date startDate, Date endDate) {
		String query = SystemGlobals.getSql("SummaryDAO.countTopicWithForumIdsByTimeLimit");
		PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(query);
		 
 			p.setTimestamp(1, new Timestamp(startDate.getTime()));	
 			p.setTimestamp(2, new Timestamp(endDate.getTime()));	
			rs = p.executeQuery();
            Map map=new HashMap();
			while(rs.next())
			{ int forum_id=rs.getInt(1);
			  int count=rs.getInt(2);
			  map.put(Integer.toString(forum_id),count);
			}
			return map;
			
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
	}

	@Override
	public int countTopicsAllByTimeLimit(Date startDate, Date endDate) {
		String query = SystemGlobals.getSql("SummaryDAO.countTopicsAllByTimeLimit");
		PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(query);
 			p.setTimestamp(1, new Timestamp(startDate.getTime()));	
 			p.setTimestamp(2, new Timestamp(endDate.getTime()));	
			rs = p.executeQuery();
            int count=0;
			if(rs.next())
				count=rs.getInt(1);
			return count;
			
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
	}

	@Override
	public List topicsByRepliesByTimeLimit(Date startDate, Date endDate,int limit) {

		String sql = SystemGlobals.getSql("SummaryDAO.topicsByRepliesByTimeLimit");

		PreparedStatement p = null;

		try {
			p = JForumExecutionContext.getConnection().prepareStatement(sql);
 			p.setTimestamp(1, new Timestamp(startDate.getTime()));	
 			p.setTimestamp(2, new Timestamp(endDate.getTime()));	
			p.setInt(3, limit);

			return this.fillTopicsData(p);
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(p);
		}
 
	}

	@Override
	public List topicsByViewsByTimeLimit(Date startDate, Date endDate,int limit) {
		String sql = SystemGlobals.getSql("SummaryDAO.topicsByViewsByTimeLimit");

		PreparedStatement p = null;

		try {
			p = JForumExecutionContext.getConnection().prepareStatement(sql);
 			p.setTimestamp(1, new Timestamp(startDate.getTime()));	
 			p.setTimestamp(2, new Timestamp(endDate.getTime()));	
			p.setInt(3, limit);

			return this.fillTopicsData(p);
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(p);
		}

	}
	/**
	 * Fills all topic data. The method will try to get all fields from the topics table, as well
	 * information about the user who made the first and the last post in the topic. <br>
	 * <b>The method <i>will</i> close the <i>PreparedStatement</i></b>
	 * 
	 * @param p the PreparedStatement to execute
	 * @return A list with all topics found
	 * @throws SQLException
	 */
	public List fillTopicsData(PreparedStatement p)
	{
		List l = new ArrayList();
		ResultSet rs = null;
		
		try {
			rs = p.executeQuery();

			SimpleDateFormat df = new SimpleDateFormat(SystemGlobals.getValue(ConfigKeys.DATE_TIME_FORMAT));

			StringBuffer sbFirst = new StringBuffer(128);
			StringBuffer sbLast = new StringBuffer(128);

			while (rs.next()) {
				Topic t = this.getBaseTopicData(rs);

				// Posted by
				User u = new User();
				u.setId(rs.getInt("user_id"));
				t.setPostedBy(u);

				// Last post by
				u = new User();
				u.setId(rs.getInt("last_user_id"));
				t.setLastPostBy(u);

				t.setHasAttach(rs.getInt("attach") > 0);
				t.setFirstPostTime(df.format(rs.getTimestamp("topic_time")));
				t.setLastPostTime(df.format(rs.getTimestamp("post_time")));
				t.setLastPostDate(new Date(rs.getTimestamp("post_time").getTime()));

				l.add(t);

				sbFirst.append(rs.getInt("user_id")).append(',');
				sbLast.append(rs.getInt("last_user_id")).append(',');
			}

			rs.close();
			rs = null;
			p.close();
			p = null;

			// Users
			if (sbFirst.length() > 0) {
				sbLast.delete(sbLast.length() - 1, sbLast.length());

				String sql = SystemGlobals.getSql("TopicModel.getUserInformation");
				sql = sql.replaceAll("#ID#", sbFirst.toString() + sbLast.toString());

				Map users = new HashMap();

				p = JForumExecutionContext.getConnection().prepareStatement(sql);
				rs = p.executeQuery();

				while (rs.next()) {
					users.put(new Integer(rs.getInt("user_id")), rs.getString("username"));
				}

				rs.close();
				rs = null;
				p.close();
				p = null;

				for (Iterator iter = l.iterator(); iter.hasNext();) {
					Topic t = (Topic) iter.next();
					t.getPostedBy().setUsername((String) users.get(new Integer(t.getPostedBy().getId())));
					t.getLastPostBy().setUsername((String) users.get(new Integer(t.getLastPostBy().getId())));
				}
			}

			return l;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
	}
	protected Topic getBaseTopicData(ResultSet rs) throws SQLException
	{
		Topic t = new Topic();

		t.setTitle(rs.getString("topic_title"));
		t.setId(rs.getInt("topic_id"));
		t.setTime(new Date(rs.getTimestamp("topic_time").getTime()));
		t.setStatus(rs.getInt("topic_status"));
		t.setTotalViews(rs.getInt("topic_views"));
		t.setTotalReplies(rs.getInt("topic_replies"));
		t.setFirstPostId(rs.getInt("topic_first_post_id"));
		t.setLastPostId(rs.getInt("topic_last_post_id"));
		t.setType(rs.getInt("topic_type"));
		t.setForumId(rs.getInt("forum_id"));
		t.setModerated(rs.getInt("moderated") == 1);
		t.setVoteId(rs.getInt("topic_vote_id"));
		t.setMovedId(rs.getInt("topic_moved_id"));
		t.setTopicGrade(rs.getInt("topic_grade"));
 		User user = new User();
		user.setId(rs.getInt("user_id"));
		
		t.setPostedBy(user);

		return t;
	}
	
}
